""""
Case Type   : D库常用语法兼容-proc
Case Name   : D库proc关键字可以作为新建表名
Create At   : 2025-11-19
Owner       : chen
Description :
    1.创建proc表,并插入数据
    2.删除表
    3.创建proc
    4.清理环境
Expect      :
    1.成功
    2.成功
    3.成功
    4.成功
History     :
"""

import os
import unittest
from testcase.utils.Logger import Logger
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from yat.test import Node

log = Logger()
primary_sh = CommonSH('PrimaryDbUser')

class DDL_Proc_Case0007(unittest.TestCase):
    def setUp(self):
        log.info(f'--------{os.path.basename(__file__)} start--------')
        self.constant = Constant()
        self.dbname_d = Node('PrimaryDbDmode').db_name

    def test_proc(self):
        text = '-----step1: 创建proc表,并插入数据; expect: 成功-----'
        log.info(text)
        msg = primary_sh.check_connection_status(self.constant.START_STATUS_MSG)
        self.assertTrue(msg)
        create_tb = f'drop table if exists proc;' \
            f'create table proc(id int ,name char(255),' \
            f'age int ,city varchar (255));'
        res = primary_sh.execut_db_sql(create_tb, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res)
        insert_sql = f"insert into proc values(1, 'zhang', 28, 'hangzhou');"
        res = primary_sh.execut_db_sql(insert_sql, dbname=self.dbname_d)
        log.info(res)
        self.assertIn('INSERT 0 1', res)
        find_sql = f'select * from proc;'
        res = primary_sh.execut_db_sql(find_sql,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn('(1 row)', res)
        self.assertIn('1 |', res)
        self.assertIn('zhang', res)
        self.assertIn('28', res)
        self.assertIn('hangzhou', res)

        text = '-----step2: 删除表; expect: 成功-----'
        log.info(text)
        drop_table = f"drop table proc;"
        res = primary_sh.execut_db_sql(drop_table,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, res)

        text = '-----step3: 创建proc; expect: 合理报错-----'
        log.info(text)
        create_proc = f"""create proc proc()
            is
            begin
            RAISE INFO 'call procedure: proc';
            end;"""
        res = primary_sh.execut_db_sql(create_proc,
                                     dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.CREATE_PROCEDURE_SUCCESS_MSG, res)
        detail_sql = '\df proc();'
        res = primary_sh.execut_db_sql(detail_sql,
                                     dbname=self.dbname_d)
        log.info(res)
        detail_list = ['public', 'proc', 'void', 'normal', 'f', 'p']
        for detail in detail_list:
            self.assertIn(detail, res)
        find_sql = f"select provolatile from pg_proc where proname = 'proc';"
        res = primary_sh.execut_db_sql(find_sql, dbname=self.dbname_d)
        log.info(res)
        self.assertIn('v', res)

    def tearDown(self):
        text = '-----step4: 清理环境; expect: 成功-----'
        log.info(text)
        drop_proc = f'''drop procedure if exists proc();'''
        res = primary_sh.execut_db_sql(drop_proc, dbname=self.dbname_d)
        log.info(res)
        self.assertIn(self.constant.DROP_PROCEDURE_SUCCESS_MSG, res)